﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace outREACH.App_Code
{
    public class NGO
    {
        public string ngoName { get; set; }
        public string ngoAddress { get; set; }
        public string ngoEmail { get; set; }
        public string ngoPhone { get; set; }
        public string ngoCountry { get; set; }
        public string ngoCity { get; set; }
        public string ngoUsername { get; set; }
        public string ngoPassword { get; set; }
        public string Zipcode { get; set; }


        public static List<NGO> GetNGOs()
        {
            SqlConnection conn = null;
            List<NGO> result = new List<NGO>();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from NGOs";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    NGO n = new NGO();
                    n.ngoName = (string)dr["ngoName"];
                    n.ngoAddress = (string)dr["ngoAddress"];
                    n.ngoEmail = (string)dr["ngoEmail"];
                    n.ngoPhone = (string)dr["ngoPhone"];
                    n.ngoCountry = (string)dr["ngoCountry"];
                    n.ngoCity = (string)dr["ngoCity"];
                    n.ngoUsername = (string)dr["ngoUsername"];
                    n.ngoPassword = (string)dr["ngoPassword"];
                    n.Zipcode = (string)dr["ZipCode"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static NGO GetName(string ngoName)
        {
            SqlConnection conn = null;
            NGO n = new NGO();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from NGOswhere ngoName = @ngoName";
                comm.Parameters.AddWithValue("@ngoName", ngoName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    n.ngoName = (string)dr["ngoName"];
                    n.ngoUsername = (string)dr["ngoUsername"];

                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return n;
        }


        public static void Insert(NGO n)
        {
            SqlConnection conn = null;
            int userID = 0;
            try
            {
                SqlCommand comm = new SqlCommand();
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO NGOs(ngoName,ngoAddress,ngoEmail,ngoPhone,ngoCountry,ngoCity,ngoUsername,ngoPassword,Zipcode) VALUES (@ngoName,@ngoAddress,@ngoEmail,@ngoCountry,@ngoCity,@ngoPhone,@ngoUsername,@ngoPassword,@ngoZipcode)";
                comm.Parameters.AddWithValue("@ngoName", n.ngoName);
                comm.Parameters.AddWithValue("@ngoAddress", n.ngoAddress);
                comm.Parameters.AddWithValue("@ngoEmail", n.ngoEmail);
                comm.Parameters.AddWithValue("@ngoPhone", n.ngoPhone);
                comm.Parameters.AddWithValue("@ngoCountry", n.ngoCountry);
                comm.Parameters.AddWithValue("@ngoCity", n.ngoCity);
                comm.Parameters.AddWithValue("@ngoUsername", n.ngoUsername);
                comm.Parameters.AddWithValue("@ngoPassword", n.ngoPassword);
                comm.Parameters.AddWithValue("@Zipcode", n.Zipcode);


                int rowsAffected = comm.ExecuteNonQuery();
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                userID = int.Parse(dr["UserID"].ToString());
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void changePassword(string ngoUsername, string ngoPassword, string ngoNewPass)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "update NgoUser set ngoPassword = @ngoNewPass where ngoUsername = @ngoUsername and ngoPassword = @ngoPassword";
                comm.Parameters.AddWithValue("@ngoUsername", ngoUsername);
                comm.Parameters.AddWithValue("@ngoPassword", ngoPassword);
                comm.Parameters.AddWithValue("@ngoNewPass", ngoNewPass);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void changeEmail(string ngoUsername, string ngoEmail, string ngoNewEmail)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "update NgoUSer set ngoEmail = @ngoNewEmail where ngoUsername = @ngoUsername";
                comm.Parameters.AddWithValue("@ngoEmail", ngoEmail);
                comm.Parameters.AddWithValue("@ngoNewEmail", ngoNewEmail);
                comm.Parameters.AddWithValue("@ngoUsername", ngoUsername);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
